package web.dao;

import web.bean.Constants;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by 愤怒的番茄 on 2014/10/8.
 */
@Repository
public class QualificationDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    /**
     *
     * @return
     * 查询已经通过审核的资质，并且key对应name
     */
//    public List<Record> findQualificationList(){
//        return Db.find("SELECT id,`name`,`key` FROM " + Constants.QUALIFICATION + " WHERE is_ok=1 and is_key=1");
//                                                     //qualification
//    }

    public List<Map<String,Object>> findQualificationList(){
        String sql="select id,`name`,`key` from "+ Constants.QUALIFICATION+" where is_ok=1 and is_key=1";
        final List<Map<String,Object>>list=new ArrayList<Map<String,Object>>();
        jdbcTemplate.query(sql,new RowCallbackHandler() {
            @Override
            public void processRow(ResultSet rs) throws SQLException {
//                Qualification qualification=new Qualification();
//                qualification.setId(rs.getInt("id"));
//                qualification.setName(rs.getString("name"));
//                qualification.setKey(rs.getString("key"));
//                list.add(qualification);
                Map<String,Object>map=new HashMap<String, Object>();
                map.put("id",rs.getInt("id"));
                map.put("name",rs.getString("name"));
                map.put("key",rs.getString("key"));
                list.add(map);
            }
        });
        return list;
    }


    /**
     *
     * @param name 资质名称
     * @return
     * 通过资质名称查询资质
     */
//    public List<Record> findQualificationOther(String name){
//    	return Db.find("SELECT id,`name`,`key` FROM " + Constants.QUALIFICATION + " WHERE is_key=2 and name=?", name);
//                                                    //qualification
//    }

    public List<Map<String,Object>> findQualificationOther(String name){
        String sql="SELECT id,`name`,`key` FROM " + Constants.QUALIFICATION + " WHERE is_key=2 and name=?";
        final List<Map<String,Object>>list=new ArrayList<Map<String,Object>>();
        jdbcTemplate.query(sql,new RowCallbackHandler() {
            @Override
            public void processRow(ResultSet rs) throws SQLException {
//                Qualification qualification=new Qualification();
//                qualification.setId(rs.getInt("id"));
//                qualification.setName(rs.getString("name"));
//                qualification.setKey(rs.getString("key"));
//                list.add(qualification);
                Map<String,Object>map=new HashMap<String, Object>();
                map.put("id",rs.getInt("id"));
                map.put("name",rs.getString("name"));
                map.put("key",rs.getString("key"));
                list.add(map);
            }

        },name);
        return list;

    }

    /**
     *
     * @param name 资质名称
     * @return
     * 通过资质名称查询资质
     */
//    public List<Record> findQualifcationName(String name){
//        return Db.find("select * from " + Constants.QUALIFICATION + " where name = ? and is_key=1", name);
//                                                 //qualification
//    }

    public List<Map<String,Object>> findQualifcationName(String name){
        String sql="select * from "+ Constants.QUALIFICATION+" where name =? and is_key=1";
        final List<Map<String,Object>>list=new ArrayList<Map<String,Object>>();
        jdbcTemplate.query(sql,new RowCallbackHandler() {
            @Override
            public void processRow(ResultSet rs) throws SQLException {
//                Qualification qualification=new Qualification();
                Map<String,Object>map=new HashMap<String, Object>();
                map.put("id",rs.getInt("id"));
                map.put("name",rs.getString("name"));
                map.put("key",rs.getString("key"));
                map.put("is_key",rs.getInt("is_key"));
                map.put("is_ok",rs.getInt("is_ok"));
                map.put("is_user",rs.getInt("is_user"));
                list.add(map);
            }
        },name);
        return  list;

    }

    /**
     *
     * @param map 保存的资质信息
     * @return
     * 向资质表中存入一条数据
     */
//    public Record add(Record record){
//        Db.save("qualification", record);
//        return record;
//    }

    public Map<String,Object> add(final Map<String,Object> map){
      final  String sql="insert into "+ Constants.QUALIFICATION+" (`name`,`key`,is_key,is_ok,is_user)values(?,?,?,?,?)";
        KeyHolder key=new GeneratedKeyHolder();
        int result=0;
        final int a=1;
        final int b=2;
        final int c=3;
        final int d=4;
        final int e=5;
//        result=jdbcTemplate.update(sql,new Object[]{map.get("id"),map.get("name"),map.get("key"),map.get("is_ok"),map.get("is_ok"),map.get("is_user")});
        result=jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
                PreparedStatement ps=con.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);
                ps.setString(a,map.get("name")+"");
                ps.setString(b,map.get("key")+"");
                ps.setInt(c,Integer.parseInt(map.get("is_key")+""));
                ps.setInt(d,Integer.parseInt(map.get("is_ok")+""));
                ps.setInt(e,Integer.parseInt(map.get("is_user")+""));
                return ps;
            }
        },key);
        if(result>0){
            map.put("id",key.getKey().intValue());
            return map;
        }
        map.put("id",0);
        return map;

    }

}
